using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
using System.Collections.ObjectModel;
using System.Collections;
using System.Text.RegularExpressions;

namespace Destra.Tools.DACGenerator.SqlSuggestor
{
    public partial class SqlSuggestor : UserControl
    {
        private const string c_serversText = "<...>";

        public SqlSuggestor()
        {
            InitializeComponent();

            ServerTextBox.ReadOnly = true;
        }



        private void SqlSuggestor_Load(object sender, EventArgs e)
        {
            LoadDatabases();
        }

        private void LoadDatabases()
        {
            DatabaseDropdown.Items.Clear();
            ObjectDropdown.Items.Clear();
            IndexDropdown.Items.Clear();
            SqlQueryTextbox.Clear();

            if (!string.IsNullOrEmpty(ServerTextBox.Text))
            {
                foreach (Database database in SqlCmoHelper.SqlDatabases(ServerTextBox.Text))
                {
                    DatabaseDropdown.Items.Add(database.Name);
                }
            }
        }

        private void DatabaseDropdown_SelectedIndexChanged(object sender, EventArgs e)
        {
            ObjectDropdown.Items.Clear();
            IndexDropdown.Items.Clear();
            SqlQueryTextbox.Clear();

            if (!string.IsNullOrEmpty(ServerTextBox.Text) && !string.IsNullOrEmpty(DatabaseDropdown.Text))
            {
                foreach (Table table in SqlCmoHelper.GetTables(ServerTextBox.Text, DatabaseDropdown.Text))
                {
                    if (!table.Name.StartsWith("sys") && !table.Name.StartsWith("dtproperties"))
                    {
                        ObjectDropdown.Items.Add(table.Schema + "." + table.Name);
                    }
                }
            }
        }

        private void ObjectDropdown_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(ServerTextBox.Text) && !string.IsNullOrEmpty(DatabaseDropdown.Text) && !string.IsNullOrEmpty(ObjectDropdown.Text))
            {
                IndexDropdown.Items.Clear();
                IndexDropdown.Items.Add("");
                foreach (Index index in SqlCmoHelper.GetViewOrTableIndexes(ServerTextBox.Text, DatabaseDropdown.Text, ObjectDropdown.Text))
                {
                    IndexDropdown.Items.Add(index.Name);
                }

                DisplaySqlQuery();
            }
        }

        private void TypeDropdown_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(ServerTextBox.Text) && !string.IsNullOrEmpty(DatabaseDropdown.Text) && !string.IsNullOrEmpty(ObjectDropdown.Text))
            {
                DisplaySqlQuery();
            }
        }

        private void IndexDropdown_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(ServerTextBox.Text) && !string.IsNullOrEmpty(DatabaseDropdown.Text) && !string.IsNullOrEmpty(ObjectDropdown.Text) && !string.IsNullOrEmpty(IndexDropdown.Text))
            {
                DisplaySqlQuery();
            }
        }

        private Index UseIndex()
        {
            switch (TypeDropdown.Text)
            {
                case "Update by Index":
                case "Delete by Index":
                case "Select by Index":
                case "Select Like Index":
                case "Select Paged by Index":
                case "Select Paged Like Index":
                    foreach (Index innerIndex in SqlCmoHelper.GetViewOrTableIndexes(ServerTextBox.Text, DatabaseDropdown.Text, ObjectDropdown.Text))
                    {
                        if (innerIndex.Name.Equals(IndexDropdown.Text))
                        {
                            return innerIndex;
                        }
                    }
                    break;
            }

            return null;
        }

        private void DisplaySqlQuery()
        {
            SqlQueryTextbox.Text = "";
            Index index = UseIndex();

            bool hasLikeIndex = false;
            if (index != null)
            {
                hasLikeIndex = SqlCmoHelper.IndexHasStringColumn(ServerTextBox.Text, DatabaseDropdown.Text, ObjectDropdown.Text, index);
            }

            Formatters.SqlFormatter sqlFormatter = new Destra.Tools.DACGenerator.Formatters.SqlFormatter();

            foreach (Column col in SqlCmoHelper.GetViewOrTableColumns(ServerTextBox.Text, DatabaseDropdown.Text, ObjectDropdown.Text))
            {
                if (col.Name.ToLowerInvariant().Equals(ObjectDropdown.Text.ToLowerInvariant()))
                {
                    MessageBox.Show("You cannot use the Sql Suggestor if any of the column names are the same as the table names.", "Sql Suggestor Errors", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
            }

            switch (TypeDropdown.Text)
            {
                case "Insert":
                    sqlFormatter.SqlText = Create();
                    SqlQueryTextbox.Text = sqlFormatter.SqlText;
                    break;
                case "Update by Index":
                    if (index != null)
                    {
                        sqlFormatter.SqlText = UpdateByIndex(index);
                        SqlQueryTextbox.Text = sqlFormatter.Format();
                    }
                    break;
                case "Delete by Index":
                    if (index != null)
                    {
                        sqlFormatter.SqlText = DeleteByIndex(index);
                        SqlQueryTextbox.Text = sqlFormatter.Format();
                    }
                    break;
                case "Select":
                    sqlFormatter.SqlText = Retrieve();
                    SqlQueryTextbox.Text = sqlFormatter.Format();
                    break;
                case "Select Paged":
                    sqlFormatter.SqlText = RetrievePagedByIndex(null);
                    SqlQueryTextbox.Text = sqlFormatter.Format();
                    break;
                case "Select by Index":
                    if (index != null)
                    {
                        sqlFormatter.SqlText = RetrieveByIndex(index, false);
                        SqlQueryTextbox.Text = sqlFormatter.Format();
                    }
                    break;
                case "Select Like Index":
                    if (index != null)
                    {
                        sqlFormatter.SqlText = RetrieveByIndex(index, hasLikeIndex);
                        SqlQueryTextbox.Text = sqlFormatter.Format();
                    }
                    break;
                case "Select Paged by Index":
                    if (index != null)
                    {
                        sqlFormatter.SqlText = RetrievePagedByIndex(index, false);
                        SqlQueryTextbox.Text = sqlFormatter.Format();
                    }
                    break;
                case "Select Paged Like Index":
                    if (index != null)
                    {
                        sqlFormatter.SqlText = RetrievePagedByIndex(index, hasLikeIndex);
                        SqlQueryTextbox.Text = sqlFormatter.Format();
                    }
                    break;
                default:
                    break;
            }
        }

        #region Datatable Related
        /*
        private static DataTable innerTable = new DataTable();

        public Collection<Index> ProjectDataTableIndexes
        {
            get
            {
                return SqlCmoHelper.GetViewOrTableIndexes(ServerDropdown.Text, DatabaseDropdown.Text, ObjectDropdown.Text);
            }
        }

        public bool IsProjectDataTablePrimaryIndex(string indexName)
        {
            return (ProjectDataTablePrimaryIndex.Name.Equals(indexName));
        }

        public Index ProjectDataTablePrimaryIndex
        {
            get
            {
                if (SqlCmoHelper.IsView(ServerDropdown.Text, DatabaseDropdown.Text, ObjectDropdown.Text))
                {
                    return ProjectDataTablePrimaryViewIndex;
                }
                else
                {
                    return ProjectDataTablePrimaryTableIndex;
                }
            }
        }

        public Index ProjectDataTablePrimaryViewIndex
        {
            get
            {
                foreach (Index index in ProjectDataTableIndexes)
                {
                    if (index.IsUnique && index.IsClustered)
                    {
                        return index;
                    }
                }
                return null;
            }
        }

        public Index ProjectDataTablePrimaryTableIndex
        {
            get
            {
                foreach (Index index in ProjectDataTableIndexes)
                {
                    if (index.IndexKeyType == IndexKeyType.DriPrimaryKey)
                    {
                        return index;
                    }
                }
                return null;
            }
        }

        public bool IsIdentitySeedColumn(string columnName)
        {
            foreach (Column col in SqlCmoHelper.GetViewOrTableColumns(ServerDropdown.Text, DatabaseDropdown.Text, ObjectDropdown.Text))
            {
                if (col.Name.ToLowerInvariant().Equals(columnName.ToLowerInvariant()))
                {
                    return col.Identity;
                }
            }
            return false;
        }
        */
        #endregion

        #region Retrieve

        private string Retrieve()
        {
            int counter = 0;
            StringBuilder sqlBuilder = new StringBuilder();

            // start our select statement
            sqlBuilder.AppendLine("SELECT");

            // use each column to set our fields
            foreach (Column col in SqlCmoHelper.GetViewOrTableColumns(ServerTextBox.Text, DatabaseDropdown.Text, ObjectDropdown.Text))
            {
                if (counter.Equals(0))
                {
                    sqlBuilder.Append("\t  ");
                }
                else
                {
                    sqlBuilder.Append("\t, ");
                }
                sqlBuilder.AppendFormat("[{0}]", col.Name);
                sqlBuilder.AppendLine();
                counter += 1;
            }

            sqlBuilder.AppendLine(string.Format("FROM [{0}].[{1}]", ObjectDropdown.Text.Split(".".ToCharArray())[0], ObjectDropdown.Text.Split(".".ToCharArray())[1]));
            return sqlBuilder.ToString();
        }

        #endregion

        #region Retrieve Paged

        private string RetrievePagedByIndex(Index index)
        {
            return RetrievePagedByIndex(index, false);
        }

        private string RetrievePagedByIndex(Index index, bool useLikeClause)
        {
            int counter = 0;
            StringBuilder sqlBuilder = new StringBuilder();

            // start our select clause
            sqlBuilder.AppendLine("SELECT");

            // now add in all of our columns with an additional column [RowNum] which will be
            // from our derived table and be like a in memory identity seed
            foreach (Column col in SqlCmoHelper.GetViewOrTableColumns(ServerTextBox.Text, DatabaseDropdown.Text, ObjectDropdown.Text))
            {
                if (counter.Equals(0))
                {
                    sqlBuilder.Append("\t  ");
                }
                else
                {
                    sqlBuilder.Append("\t, ");
                }
                sqlBuilder.AppendFormat("[{0}]", col.Name);
                sqlBuilder.AppendLine();
                counter += 1;
            }
            sqlBuilder.AppendLine("\t, RowNum");

            sqlBuilder.AppendLine("FROM (");

            // add in our sub select clause
            sqlBuilder.AppendLine("\t SELECT");

            // add all of the columns for our subselect
            counter = 0;
            foreach (Column col in SqlCmoHelper.GetViewOrTableColumns(ServerTextBox.Text, DatabaseDropdown.Text, ObjectDropdown.Text))
            {
                if (counter.Equals(0))
                {
                    sqlBuilder.Append("\t\t  ");
                }
                else
                {
                    sqlBuilder.Append("\t\t, ");
                }
                sqlBuilder.AppendFormat("[{0}]", col.Name);
                sqlBuilder.AppendLine();
                counter += 1;
            }

            // the magic part is the ROW_NUMBER() which will give an identity seeded number to each row in the selection
            if (index == null)
            {
                sqlBuilder.AppendFormat("\t\t, ROW_NUMBER() OVER(ORDER BY {0}) AS RowNum", SqlCmoHelper.GetViewOrTableColumns(ServerTextBox.Text, DatabaseDropdown.Text, ObjectDropdown.Text)[0].Name);
                sqlBuilder.AppendLine();
            }
            else
            {
                sqlBuilder.AppendFormat("\t\t, ROW_NUMBER() OVER(ORDER BY {0}) AS RowNum", index.IndexedColumns[0]);
                sqlBuilder.AppendLine();
            }

            // set the table name
            sqlBuilder.AppendFormat("\t FROM [{0}].[{1}]", ObjectDropdown.Text.Split(".".ToCharArray())[0], ObjectDropdown.Text.Split(".".ToCharArray())[1]);
            sqlBuilder.AppendLine();

            if (index != null)
            {
                // now being our where clause
                sqlBuilder.Append("\t WHERE ");

                // generate our where clause based on the indexes
                counter = 0;
                foreach (IndexedColumn col in index.IndexedColumns)
                {
                    if (!counter.Equals(0))
                    {
                        sqlBuilder.Append("\t\tAND ");
                    }

                    if (useLikeClause)
                    {
                        sqlBuilder.AppendFormat("[{0}] LIKE @{1}", col.Name, StringHelper.FormatPascal(StringHelper.FormatColumnName(col.Name)));
                    }
                    else
                    {
                        sqlBuilder.AppendFormat("[{0}] = @{1}", col.Name, StringHelper.FormatPascal(StringHelper.FormatColumnName(col.Name)));
                    }
                    sqlBuilder.AppendLine();
                    counter += 1;
                }
            }

            // finally set the AS [TableName] syntax
            sqlBuilder.AppendFormat("\t) AS [{0}Derived]", ObjectDropdown.Text);
            sqlBuilder.AppendLine();

            // now filter on our dynamic row count
            sqlBuilder.AppendLine("WHERE RowNum BETWEEN CAST(@startRowIndex AS BIGINT) AND (CAST(@startRowIndex AS BIGINT) + CAST(@maximumRows AS BIGINT))");
            return sqlBuilder.ToString();
        }

        #endregion

        #region Retrieve By Index

        private string RetrieveByIndex(Index index, bool useLikeClause)
        {
            int counter = 0;
            StringBuilder sqlBuilder = new StringBuilder();

            // add our standard select statement
            sqlBuilder.Append(Retrieve());

            // now being our where clause
            sqlBuilder.Append("WHERE ");

            // generate our where clause based on the indexes
            foreach (IndexedColumn col in index.IndexedColumns)
            {
                if (!counter.Equals(0))
                {
                    sqlBuilder.Append("\tAND ");
                }
                if (useLikeClause)
                {
                    sqlBuilder.AppendFormat("[{0}] LIKE @{1}", col.Name, StringHelper.FormatPascal(StringHelper.FormatColumnName(col.Name)));
                }
                else
                {
                    sqlBuilder.AppendFormat("[{0}] = @{1}", col.Name, StringHelper.FormatPascal(StringHelper.FormatColumnName(col.Name)));
                }
                sqlBuilder.AppendLine();
                counter += 1;
            }

            // check to make sure that we have at least 1 where clause
            if (sqlBuilder.ToString().Trim().EndsWith("WHERE"))
            {
                throw new SyntaxErrorException("Table " + ObjectDropdown.Text + " has no known Indexes for " + index.Name + ", unable to generate RetrieveByIndex Sql");
            }

            return sqlBuilder.ToString();
        }

        #endregion

        #region Update By PrimaryKey

        private string UpdateByIndex(Index index)
        {
            int counter = 0;
            StringBuilder sqlBuilder = new StringBuilder();

            // start our update and set statements
            sqlBuilder.AppendFormat("UPDATE [{0}].[{1}]", ObjectDropdown.Text.Split(".".ToCharArray())[0], ObjectDropdown.Text.Split(".".ToCharArray())[1]);
            sqlBuilder.AppendLine();
            sqlBuilder.Append("SET");

            // generate our set clause based on our primary keys
            counter = 0;
            foreach (Column col in SqlCmoHelper.GetViewOrTableColumns(ServerTextBox.Text, DatabaseDropdown.Text, ObjectDropdown.Text))
            {
                if (!col.Identity && !SqlCmoHelper.IndexContainsColumnName(index.IndexedColumns, col.Name))
                {
                    if (counter.Equals(0))
                    {
                        sqlBuilder.Append("\t  ");
                    }
                    else
                    {
                        sqlBuilder.Append("\t, ");
                    }
                    sqlBuilder.AppendFormat("[{0}] = @{1}", col.Name, StringHelper.FormatPascal(StringHelper.FormatColumnName(col.Name)));
                    sqlBuilder.AppendLine();
                    counter += 1;
                }
            }

            // being our where clause
            sqlBuilder.Append("WHERE ");

            // dynamically set our where clause based on the primary keys
            counter = 0;
            // loop all of our primary keys building a dynamic where clause
            foreach (IndexedColumn col in index.IndexedColumns)
            {
                if (!counter.Equals(0))
                {
                    sqlBuilder.Append("\tAND ");
                }
                sqlBuilder.AppendFormat("[{0}] = @{1}", col.Name, StringHelper.FormatPascal(StringHelper.FormatColumnName(col.Name)));
                sqlBuilder.AppendLine();
                counter += 1;
            }

            // do a final sanity check to make sure we've added a property
            if (sqlBuilder.ToString().EndsWith("WHERE "))
            {
                throw new SyntaxErrorException("Table " + ObjectDropdown.Text + " has no known PrimaryKeys, unable to generate Update Sql");
            }

            return sqlBuilder.ToString();
        }

        #endregion

        #region Delete By PrimaryKey

        private string DeleteByIndex(Index index)
        {
            int counter = 0;
            StringBuilder sqlBuilder = new StringBuilder();

            // start our delete statement
            sqlBuilder.AppendFormat("DELETE FROM [{0}].[{1}]", ObjectDropdown.Text.Split(".".ToCharArray())[0], ObjectDropdown.Text.Split(".".ToCharArray())[1]);
            sqlBuilder.AppendLine();

            // begin our where clause
            sqlBuilder.Append("WHERE ");

            // loop all of our primary keys building a dynamic where clause
            foreach (IndexedColumn col in index.IndexedColumns)
            {
                if (!counter.Equals(0))
                {
                    sqlBuilder.Append("\tAND ");
                }
                sqlBuilder.AppendFormat("[{0}] = @{1}", col.Name, StringHelper.FormatPascal(StringHelper.FormatColumnName(col.Name)));
                sqlBuilder.AppendLine();
                counter += 1;
            }

            // check to make sure that we have at least 1 where clause
            if (sqlBuilder.ToString().EndsWith("WHERE "))
            {
                throw new SyntaxErrorException("Table " + ObjectDropdown.Text + " has no known Indexes for " + index.Name + ", unable to generate DeleteByIndex Sql");
            }

            return sqlBuilder.ToString();
        }

        #endregion

        #region Create

        public string Create()
        {
            int counter = 0;
            StringBuilder sqlBuilder = new StringBuilder();

            // start our select statement
            if (SqlCmoHelper.HasIdentitySeedColumn(ServerTextBox.Text, DatabaseDropdown.Text, ObjectDropdown.Text))
            {
                sqlBuilder.AppendLine("-- Variable used to return the newly inserted identity seed.");
                sqlBuilder.AppendLine("DECLARE @outputIdentitySeed AS INT");
            }

            sqlBuilder.AppendFormat("INSERT INTO [{0}].[{1}] (", ObjectDropdown.Text.Split(".".ToCharArray())[0], ObjectDropdown.Text.Split(".".ToCharArray())[1]);
            sqlBuilder.AppendLine();

            // get our list of columns
            Collection<Column> columns = SqlCmoHelper.GetViewOrTableColumns(ServerTextBox.Text, DatabaseDropdown.Text, ObjectDropdown.Text);
           
            // create our list of insert columns
            counter = 0;
            foreach (Column col in columns)
            {
                if (!col.Identity)
                {
                    if (counter.Equals(columns.Count - 1))
                    {
                        sqlBuilder.AppendFormat("\t[{0}] ", col.Name);
                    }
                    else
                    {
                        sqlBuilder.AppendFormat("\t[{0}] , ", col.Name);
                    }
                    sqlBuilder.AppendLine();
                }
                counter += 1;
            }
            sqlBuilder.AppendLine(")");
            sqlBuilder.AppendLine("VALUES (");

            counter = 0;
            foreach (Column col in columns)
            {
                if (!col.Identity)
                {
                    if (counter.Equals(columns.Count - 1))
                    {
                        sqlBuilder.AppendFormat("\t@{0} ", StringHelper.FormatPascal(StringHelper.FormatColumnName(col.Name)));
                    }
                    else
                    {
                        sqlBuilder.AppendFormat("\t@{0} , ", StringHelper.FormatPascal(StringHelper.FormatColumnName(col.Name)));
                    }
                    sqlBuilder.AppendLine();
                }
                counter += 1;
            }
            sqlBuilder.AppendLine(")");
            sqlBuilder.AppendLine("");

            // return our identity seed if required
            if (SqlCmoHelper.HasIdentitySeedColumn(ServerTextBox.Text, DatabaseDropdown.Text, ObjectDropdown.Text))
            {
                sqlBuilder.AppendLine("SET @outputIdentitySeed = SCOPE_IDENTITY()");
                sqlBuilder.AppendLine("SELECT @outputIdentitySeed AS 'Identity' ");
            }

            return sqlBuilder.ToString();
        }

        #endregion

        public string SQL
        {
            get
            {
                return SqlQueryTextbox.Text;
            }
        }

        public string Server
        {
            set
            {
                ServerTextBox.Text = value;

                LoadDatabases();
            }
        }

        public string Database
        {
            get
            {
                return (string)DatabaseDropdown.SelectedItem;
            }
            set
            {
                DatabaseDropdown.SelectedItem = value;
            }
        }

        public string Object
        {
            get
            {
                return (string)ObjectDropdown.SelectedItem;
            }
            set
            {
                ObjectDropdown.SelectedItem = value;
            }
        }
    }

    #region String Helper Class

    public static class StringHelper
    {

        /// <summary>
        /// 
        /// </summary>
        /// <param name="level"></param>
        /// <returns></returns>
        internal static string Tab(int level)
        {
            StringBuilder indentBuilder = new StringBuilder();
            for (int i = 0; i < level; i++)
            {
                indentBuilder.Append("\t");
            }
            return indentBuilder.ToString();
        }

        internal static string FormatIndexName(Index index)
        {
            if (!index.IsIndexOnTable)
            {
                return index.Name;
            }
            return FormatIndexName(index.Name, ((Table)index.Parent).Name);
        }

        internal static string FormatIndexName(string indexName, string tableName)
        {
            string originalIndexName = indexName;

            if (indexName.StartsWith("IX_"))
            {
                indexName = indexName.Substring(2);
            }

            if (indexName.StartsWith("PK_"))
            {
                indexName = "PrimaryKey" + indexName.Substring(2);
            }

            if (string.IsNullOrEmpty(indexName.Replace(tableName, "").Replace("_", "")))
            {
                throw new Microsoft.SqlServer.Management.Smo.MissingObjectException("There is an invalid Index Name on " + tableName + " >> '" + originalIndexName + "'");
            }

            return indexName.Replace(tableName, "").Replace("_", "");
        }

        /// <summary>
        /// Removes any invalid charaters from a column name
        /// </summary>
        /// <param name="original">A string to be cleaned.</param>
        /// <returns>A cleaned string</returns>
        internal static string FormatColumnName(string original)
        {
            return Regex.Replace(original, @"\d", "");
        }

        /// <summary>
        /// Cleans whitespace inside a string.
        /// </summary>
        /// <param name="original">A string to be cleaned.</param>
        /// <returns>A cleaned string.</returns>
        internal static string CleanWhitespace(string original)
        {
            if (original.Length > 0)
            {
                return Regex.Replace(original, @"[\s]", String.Empty);
            }
            else
            {
                return String.Empty;
            }
        }

        /// <summary>
        /// Formats a string in Camel case (the first letter is in lower case).
        /// </summary>
        /// <param name="original">A string to be formatted.</param>
        /// <returns>A string in Camel case.</returns>
        internal static string FormatCamel(string original)
        {
            if (original.Length > 0)
            {
                return original.Substring(0, 1).ToLower() + original.Substring(1);
            }
            else
            {
                return String.Empty;
            }
        }

        /// <summary>
        /// Formats a string in Pascal case (the first letter is in upper case).
        /// </summary>
        /// <param name="original">A string to be formatted.</param>
        /// <returns>A string in Pascal case.</returns>
        internal static string FormatPascal(string original)
        {
            if (original.Length > 0)
            {
                return original.Substring(0, 1).ToUpper() + original.Substring(1);
            }
            else
            {
                return String.Empty;
            }
        }
    }

    #endregion

    #region SqlCmo Helper Class

    public static class SqlCmoHelper
    {

        public static DatabaseCollection SqlDatabases(string serverName)
        {
            Server theServer = new Server(serverName);
            return theServer.Databases;
        }

        public static TableCollection GetTables(string serverName, string databaseName)
        {
            foreach (Database db in SqlDatabases(serverName))
            {
                if (db.Name.ToLowerInvariant().Equals(databaseName.ToLowerInvariant()))
                {
                    return db.Tables;
                }
            }
            return null;
        }

        public static Table GetTable(string serverName, string databaseName, string schemaOwnerName, string tableName)
        {
            foreach (Database db in SqlDatabases(serverName))
            {
                if (db.Name.ToLowerInvariant().Equals(databaseName.ToLowerInvariant()))
                {
                    foreach (Table tbl in db.Tables)
                    {
                        if (tbl.Name.ToLowerInvariant().Equals(tableName.ToLowerInvariant()) && tbl.Schema.ToLowerInvariant().Equals(schemaOwnerName.ToLowerInvariant()))
                        {
                            return tbl;
                        }
                    }
                }
            }
            return null;
        }

        public static Microsoft.SqlServer.Management.Smo.View GetView(string serverName, string databaseName, string viewName)
        {
            foreach (Database db in SqlDatabases(serverName))
            {
                if (db.Name.ToLowerInvariant().Equals(databaseName.ToLowerInvariant()))
                {
                    foreach (Microsoft.SqlServer.Management.Smo.View view in db.Views)
                    {
                        if (view.Name.ToLowerInvariant().Equals(viewName.ToLowerInvariant()))
                        {
                            return view;
                        }
                    }
                }
            }
            return null;
        }

        public static bool IsView(string serverName, string databaseName, string objectName)
        {
            return (GetView(serverName, databaseName, objectName) != null);
        }

        public static bool IndexContainsColumnName(IndexedColumnCollection columns, string columnName)
        {
            foreach (IndexedColumn col in columns)
            {
                if (col.Name.ToLowerInvariant().Equals(columnName.ToLowerInvariant()))
                {
                    return true;
                }
            }
            return false;
        }

        public static DataColumn GetViewOrTableColumn(DataTable table, string columnName)
        {
            foreach (DataColumn col in table.Columns)
            {
                if (col.ColumnName.ToLowerInvariant().Equals(columnName.ToLowerInvariant()))
                {
                    return col;
                }
            }
            return null;
        }

        public static Column GetViewOrTableColumn(string serverName, string databaseName, string objectName, string columnName)
        {
            foreach (Column col in GetViewOrTableColumns(serverName, databaseName, objectName))
            {
                if (col.Name.ToLowerInvariant().Equals(columnName.ToLowerInvariant()))
                {
                    return col;
                }
            }
            return null;
        }

        public static Collection<Column> GetViewOrTableColumns(string serverName, string databaseName, string objectName)
        {
            // we dont want to return a ColumnCollection because it is an abstract class
            // and we don't want to return null from this method call
            Collection<Column> ret = new Collection<Column>();
            if (IsView(serverName, databaseName, objectName))
            {
                foreach (Column col in GetView(serverName, databaseName, objectName).Columns)
                {
                    ret.Add(col);
                }
            }
            else
            {
                foreach (Column col in GetTable(serverName, databaseName, objectName.Split(".".ToCharArray())[0], objectName.Split(".".ToCharArray())[1]).Columns)
                {
                    ret.Add(col);
                }
            }
            return ret;
        }

        public static Collection<Index> GetViewOrTableIndexes(string serverName, string databaseName, string objectName)
        {
            // we dont want to return a IndexCollection because it is an abstract class
            // and we don't want to return null from this method call
            Collection<Index> ret = new Collection<Index>();
            if (IsView(serverName, databaseName, objectName))
            {
                foreach (Index index in GetView(serverName, databaseName, objectName).Indexes)
                {
                    ret.Add(index);
                }
            }
            else
            {
                foreach (Index index in GetTable(serverName, databaseName, objectName.Split(".".ToCharArray())[0], objectName.Split(".".ToCharArray())[1]).Indexes)
                {
                    ret.Add(index);
                }
            }
            return ret;
        }

        public static Collection<ForeignKey> GetViewOrTableForeignKey(string serverName, string databaseName, string objectName)
        {
            // we dont want to return a ForeignKeyCollection because it is an abstract class
            // and we don't want to return null from this method call
            Collection<ForeignKey> ret = new Collection<ForeignKey>();
            if (IsView(serverName, databaseName, objectName))
            {
                // views can't have any foreign keys
            }
            else
            {
                foreach (ForeignKey foreignKey in GetTable(databaseName, serverName, objectName.Split(".".ToCharArray())[0], objectName.Split(".".ToCharArray())[1]).ForeignKeys)
                {
                    ret.Add(foreignKey);
                }
            }
            return ret;
        }

        public static bool HasIdentitySeedColumn(string serverName, string databaseName, string objectName)
        {
            foreach (Column col in SqlCmoHelper.GetViewOrTableColumns(serverName, databaseName, objectName))
            {
                if (col.Identity)
                {
                    return true;
                }
            }
            return false;
        }


        public static Column GetIdentitySeedColumn(string serverName, string databaseName, string objectName)
        {
            foreach (Column col in SqlCmoHelper.GetViewOrTableColumns(serverName, databaseName, objectName))
            {
                if (col.Identity)
                {
                    return col;
                }
            }
            return null;
        }

        public static bool IndexColumnIsStringType(string serverName, string databaseName, IndexedColumn col)
        {
            if (!col.Parent.IsIndexOnTable)
            {
                return false;
            }
            Table table = (Table)col.Parent.Parent;
            Column column = GetViewOrTableColumn(serverName, databaseName, table.Name, col.Name);
            switch (column.DataType.SqlDataType)
            {
                case SqlDataType.Char:
                case SqlDataType.NChar:
                case SqlDataType.NVarChar:
                case SqlDataType.NVarCharMax:
                case SqlDataType.UniqueIdentifier:
                case SqlDataType.VarChar:
                case SqlDataType.VarCharMax:
                    return true;
            }

            return false;
        }

        public static bool IndexHasStringColumn(string serverName, string databaseName, string objectName, Index index)
        {
            foreach (IndexedColumn col in index.IndexedColumns)
            {
                Column innerCol = GetViewOrTableColumn(serverName, databaseName, objectName, col.Name);

                if (innerCol.DataType.ToString().ToLowerInvariant().Equals("nchar")
                        || innerCol.DataType.ToString().ToLowerInvariant().Equals("char")
                        || innerCol.DataType.ToString().ToLowerInvariant().Equals("ntext")
                        || innerCol.DataType.ToString().ToLowerInvariant().Equals("nvarchar")
                        || innerCol.DataType.ToString().ToLowerInvariant().Equals("nvarcharmax")
                        || innerCol.DataType.ToString().ToLowerInvariant().Equals("text")
                        || innerCol.DataType.ToString().ToLowerInvariant().Equals("varchar")
                        || innerCol.DataType.ToString().ToLowerInvariant().Equals("varcharmax"))
                {
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// Convert a base data type to another base data type
        /// </summary>
        public sealed class DataTypeConvertor
        {

            private struct DbTypeMapEntry
            {
                public Type Type;
                public DbType DbType;
                public SqlDbType SqlDbType;
                public DbTypeMapEntry(Type type, DbType dbType, SqlDbType sqlDbType)
                {
                    this.Type = type;
                    this.DbType = dbType;
                    this.SqlDbType = sqlDbType;
                }
            };

            private static ArrayList _DbTypeList = new ArrayList();

            #region Constructors

            static DataTypeConvertor()
            {
                _DbTypeList.Add(new DbTypeMapEntry(typeof(bool), DbType.Boolean, SqlDbType.Bit));
                _DbTypeList.Add(new DbTypeMapEntry(typeof(byte), DbType.Double, SqlDbType.TinyInt));
                _DbTypeList.Add(new DbTypeMapEntry(typeof(byte[]), DbType.Binary, SqlDbType.Image));
                _DbTypeList.Add(new DbTypeMapEntry(typeof(DateTime), DbType.DateTime, SqlDbType.DateTime));
                _DbTypeList.Add(new DbTypeMapEntry(typeof(Decimal), DbType.Decimal, SqlDbType.Decimal));
                _DbTypeList.Add(new DbTypeMapEntry(typeof(double), DbType.Double, SqlDbType.Float));
                _DbTypeList.Add(new DbTypeMapEntry(typeof(Guid), DbType.Guid, SqlDbType.UniqueIdentifier));
                _DbTypeList.Add(new DbTypeMapEntry(typeof(Int16), DbType.Int16, SqlDbType.SmallInt));
                _DbTypeList.Add(new DbTypeMapEntry(typeof(Int32), DbType.Int32, SqlDbType.Int));
                _DbTypeList.Add(new DbTypeMapEntry(typeof(Int64), DbType.Int64, SqlDbType.BigInt));
                _DbTypeList.Add(new DbTypeMapEntry(typeof(object), DbType.Object, SqlDbType.Variant));
                _DbTypeList.Add(new DbTypeMapEntry(typeof(string), DbType.String, SqlDbType.VarChar));
            }

            private DataTypeConvertor()
            {
            }

            #endregion

            #region Methods
            /// <summary>
            /// Convert db type to .Net data type
            /// </summary>
            /// <param name="dbType"></param>
            /// <returns></returns>
            public static Type ToNetType(DbType dbType)
            {
                DbTypeMapEntry entry = Find(dbType);
                return entry.Type;
            }

            /// <summary>
            /// Convert TSQL type to .Net data type
            /// </summary>
            /// <param name="sqlDbType"></param>
            /// <returns></returns>
            public static Type ToNetType(SqlDbType sqlDbType)
            {
                DbTypeMapEntry entry = Find(sqlDbType);
                return entry.Type;
            }

            /// <summary>
            /// Convert .Net type to Db type
            /// </summary>
            /// <param name="type"></param>
            /// <returns></returns>
            public static DbType ToDbType(Type type)
            {
                DbTypeMapEntry entry = Find(type);
                return entry.DbType;
            }

            /// <summary>
            /// Convert TSQL data type to DbType
            /// </summary>
            /// <param name="sqlDbType"></param>
            /// <returns></returns>
            public static DbType ToDbType(SqlDbType sqlDbType)
            {
                DbTypeMapEntry entry = Find(sqlDbType);
                return entry.DbType;
            }

            /// <summary>
            /// Convert .Net type to TSQL data type
            /// </summary>
            /// <param name="type"></param>
            /// <returns></returns>
            public static SqlDbType ToSqlDbType(Type type)
            {
                DbTypeMapEntry entry = Find(type);
                return entry.SqlDbType;
            }

            /// <summary>
            /// Convert DbType type to TSQL data type
            /// </summary>
            /// <param name="dbType"></param>
            /// <returns></returns>
            public static SqlDbType ToSqlDbType(DbType dbType)
            {
                DbTypeMapEntry entry = Find(dbType);
                return entry.SqlDbType;
            }

            private static DbTypeMapEntry Find(Type type)
            {
                object retObj = null;
                for (int i = 0; i < _DbTypeList.Count; i++)
                {
                    DbTypeMapEntry entry = (DbTypeMapEntry)_DbTypeList[i];
                    if (entry.Type == type)
                    {
                        retObj = entry;
                        break;
                    }
                }
                if (retObj == null)
                {
                    throw new ApplicationException("Referenced an unsupported Type");
                }
                return (DbTypeMapEntry)retObj;
            }

            private static DbTypeMapEntry Find(DbType dbType)
            {

                object retObj = null;
                for (int i = 0; i < _DbTypeList.Count; i++)
                {
                    DbTypeMapEntry entry = (DbTypeMapEntry)_DbTypeList[i];
                    if (entry.DbType == dbType)
                    {
                        retObj = entry;
                        break;
                    }
                }

                if (retObj == null)
                {
                    throw new ApplicationException("Referenced an unsupported DbType");

                }
                return (DbTypeMapEntry)retObj;
            }

            private static DbTypeMapEntry Find(SqlDbType sqlDbType)
            {
                object retObj = null;
                for (int i = 0; i < _DbTypeList.Count; i++)
                {
                    DbTypeMapEntry entry = (DbTypeMapEntry)_DbTypeList[i];
                    if (entry.SqlDbType == sqlDbType)
                    {
                        retObj = entry;
                        break;
                    }
                }

                if (retObj == null)
                {
                    throw new ApplicationException("Referenced an unsupported SqlDbType");
                }

                return (DbTypeMapEntry)retObj;
            }

            #endregion

        }
    }


    #endregion

}
